﻿using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using WPFExcelReport.Helpers;
using System.Data.OracleClient;
using System.IO;
using INTEX.DataBase.Tools;
using INTEX.DataBase;
using System.Linq;
namespace WPFExcelReport
{
    public partial class YCAEK03DOCReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        public string  DocType="";
        SETINTEXDB INTEXDB = new INTEX.DataBase.SETINTEXDB();
        public bool IsApplyPayment = false;
        decimal EKKWE1 = 0;
        decimal EKKME1 = 0;
        string abaid = "";
        #endregion
        public YCAEK03DOCReport()
            : base()
        {
        }
 
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {
                if (exAgent == null)
                    exAgent = new ExcelX.ExcelLib.ExcelAgent();
                //exAgent._app.Visible = true;
                Helper.GetDate(ref date, ref year, ref month, ref day);
                Root = @"C:\IntexReport";
                strCompany = "YCA";
                strExcelFile = @"YCA\YCAEK.xls";
                Standard = "EK03DOC";
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\YCA\YCAEK.xls");
                string FSARTPRGR = "";
                string FYEAR = "";
                if (dsSource.Tables["ICC_ABAUFM"].Rows.Count > 0)
                {
                    abaid = dsSource.Tables["ICC_ABAUFM"].Rows[0]["ABAID"].ToString();
                    FYEAR = dsSource.Tables["ICC_ABAUFM"].Rows[0]["ABPART"].ToString().Substring(0, 2);
                    //destfolder = SetFolder(destfolder, FYEAR);
                    FSARTPRGR = dsSource.Tables["ICC_ABAUFM"].Rows[0]["FSARTPRGR"].ToString().Substring(0, 2);
                    destfolder = SetFolder(destfolder, FYEAR + FSARTPRGR);
                } 
                DataRow dwData = INTEXDB.SDDAT.NewRow();
                dwData["SDSA"] = "0255";
                DataSet SResult = INTEXDBC.QuerySDDAT(dwData, 9999);
                dsSource.Merge(SResult);

                foreach (DataRow row in dsSource.Tables["EKKOPF"].Rows)
                {
                    if (dsSource.Tables.Contains("EKVRECH"))
                    {
                        dsSource.Tables["EKVRECH"].Clear();
                    }
                    DataRow rowTemp = INTEXDB.EKVRECH.NewRow();
                    rowTemp["EKVMANDANT"] = row["EKKMANDANT"];
                    rowTemp["EKVRECHNR"] = row["EKKAUFKRS"].ToString() + "-" + row["EKKAUFLFD"].ToString();
                    SResult = INTEXDBC.QueryEKVRECH(rowTemp, 2);
                    dsSource.Merge(SResult);

                    string KRS = row["EKKAUFKRS"].ToString().Trim();
                    string LFD = row["EKKAUFLFD"].ToString().Trim();
                    string ktype = row["EKKBUCHKRS"].ToString().Trim();
                    string at = row["EKKARTGR"].ToString().Trim();
                    string lief = row["EKKLIEFNR"].ToString();
                    var queyr = from DataRow rl in dsSource.Tables["LIEFDAT"].AsEnumerable()
                                where rl["LSLIEFNR"].ToString() == lief
                                select rl;
                    foreach (DataRow rl in queyr)
                    {
                        lief = rl["LSLIEFNA"].ToString();
                    }
                    lief = Helper.GetSubRollNum(lief);
                    LFD = Helper.GetSubRollNum(LFD);
                    switch (ktype)
                    {
                        case "XH":
                            Standard = "EK03DOC";
                            abaid = "";
                            destfolder = SetFolder(Root, "采购单");
                            if (at == "5")
                            {
                                Standard = "EK03FP";
                            }
                            break;
                        case "PO":
                            Standard = "EK03PO";
                            abaid = "";
                            destfolder = SetFolder(Root, "采购单");
                            break;
                    
                        default:
                            Standard = "EK03DOC";
                            break;
                    }
                    if (DocType == "EKSEND")
                    {
                        destfolder = SetFolder(Root, "发料单");
                        Standard = "EKSEND";
                    }
                    //Standard = "EK03PO";
                    string newfilename = KRS.Trim() + LFD.ToString();
                    exAgent.CheckWorkBook(newfilename);
                    ServerShareFile("-" + FSARTPRGR + "-" + lief + "-" + KRS + "-" + LFD, fileType, row);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                exAgent.Save();
                exAgent.Close();
                exAgent = null;
            }
        }
        private void ServerShareFile(string ordernumer, string fileType, DataRow row)
        {
            string destfile, tempfile = "";
            ordernumer = ordernumer + abaid;
            string fileabsname = ordernumer;
            try
            {
                filename = destfolder + "\\采购单" + fileabsname + ".xls"; ;
                if (DocType == "EKSEND")
                {
                    filename = destfolder + "\\发料单" + fileabsname + ".xls"; ;
                }
               
                destfile = filename;
                if (File.Exists(fileabsname + ".xls"))
                {
                    File.Delete(fileabsname + ".xls");
                }
                tempfile = CreateFile(ordernumer, row);
                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
        private int FillInEKPOS()
        {
            int x = 1, y = 1;
            int rown = 20;
            string value = "";
            string artdes = "", colordes = "";
            var qek = from DataRow rek in dsSource.Tables["ICC_EKPOS"].AsEnumerable()
                      orderby rek["EKPAUFPOS"].ToString() ascending
                      select rek;
            foreach (DataRow dwEKPOS in qek)
            {
                decimal applied = 0;
                decimal paied = 0;
                string ordernum = Helpers.Helper.Left(dwEKPOS["EKPAUFKRS"].ToString() + "-" + dwEKPOS["EKPAUFLFD"].ToString() + "-" + dwEKPOS["EKPAUFPOS"].ToString() + "                       ", 23);
                var queryx = from DataRow row in dsSource.Tables["EKVRECH"].AsEnumerable()
                            where row["EKVFILLER"].ToString() == ordernum
                            select row;
                foreach (DataRow row in queryx)
                {
                    if (row["EKVKIR"].ToString() == "K")
                    {
                        paied += System.Convert.ToDecimal(row["EKVBETRAG"].ToString()) / 100;
                    }
                    if (row["EKVKIR"].ToString() == " ")
                    {
                        applied += System.Convert.ToDecimal(row["EKVBETRAG"].ToString()) / 100;
                    }

                }
                if (IsApplyPayment && applied == 0)
                {
                    continue;
                }
                if (rown > 21)
                {
                    exAgent.RangeCopy(21, 23, rown + 1, rown + 3);
                } 
                rown++;
                if (applied > 0)
                {
                    exAgent.SetCellValue(rown+1, 32, "Q:" + applied.ToString());
                }
                if (paied > 0)
                {
                    exAgent.SetCellValue(rown+2, 32, "F:" + paied.ToString());
                }
                DataRow dwArticle = null;
                DataRow dwColor = null;
                string exart = "";
                int pos = System.Convert.ToInt16(dwEKPOS["EKPAUFPOS"].ToString());
                string artg = dwEKPOS["EKPARTGR"].ToString();
                string art = dwEKPOS["EKPART"].ToString();
                string var = dwEKPOS["EKPVAR"].ToString();
                var = Left(var, 2);
                string structure = "";
                string composition = "";
                string excolor = "";
                string color = dwEKPOS["EKPFARBE"].ToString();
                string size = dwEKPOS["EKPGROE"].ToString();
                string cref = dwEKPOS["EKPLSGRPBEZ"].ToString().Trim();
                string um = dwEKPOS["EKPMEEH"].ToString();
                double qty = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPME1"].ToString()) * 0.01, 2);
                double tvalue = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPWE1"].ToString()) * 0.01, 2);
                double price = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPPRME"].ToString()) * 0.001, 2);
                double gsqm = 1;
                double width = 1;

                value = dwEKPOS["EKPLTERM"].ToString();
                exAgent.SetCellValue(4, 30, value);
                var query = from rows in dsSource.Tables["FSSTAMM"].AsEnumerable()
                            where rows["FSART"].ToString() == art
                            select rows;
                switch (artg)
                {
                    case "1":
                        break;
                    case "2":
                        break;
                    case "3":
                        query = from rows in dsSource.Tables["WASTAMM"].AsEnumerable()
                                where rows["WAART"].ToString() == art
                                & rows["WAVAR"].ToString() == var
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwArticle = rw;
                            artdes = GetGB2312Value(rw["WABEZ"].ToString());
                            exart = GetGB2312Value(dwArticle["WALOHNA"].ToString().Trim());
                            width = System.Math.Round(System.Convert.ToDouble(dwArticle["WAFBRTL"].ToString()) * 0.1, 0);
                            gsqm = System.Math.Round(System.Convert.ToDouble(dwArticle["WAFGRQM"].ToString()), 0);
                            structure = //System.Math.Round(System.Convert.ToDouble(dwArticle["WAFFDDIK"].ToString()) * 0.01, 0)
                                //+ "X" + System.Math.Round(System.Convert.ToDouble(dwArticle["WAFFDDIS"].ToString()) * 0.01, 0)
                             "幅宽:" + width + "cm"
                            + "," + gsqm + "g/sqm"
                            ;

                        }
                        composition = Helper.GetComposition(art, dsSource.Tables["STUELI"]);
                        query = from rows in dsSource.Tables["WAFARBEN"].AsEnumerable()
                                where rows["WAFART"].ToString() == art
                                & rows["WAFVAR"].ToString() == var
                               & rows["WAFZUSATZ"].ToString() == color
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwColor = rw;
                            colordes = GetGB2312Value(rw["WAFBEZ"].ToString());
                            excolor = GetGB2312Value(rw["WAFMUDESS"].ToString());
                        }
                        break;

                    case "4":
                        break;
                    case "5":
                        query = from rows in dsSource.Tables["FSSTAMM"].AsEnumerable()
                                where rows["FSART"].ToString() == art
                                & rows["FSVAR"].ToString() == var
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwArticle = rw;
                            artdes = GetGB2312Value(rw["FSABEZ"].ToString());
                        }
                        query = from rows in dsSource.Tables["FSFARBEN"].AsEnumerable()
                                where rows["FSFART"].ToString() == art
                                & rows["FSFVAR"].ToString() == var
                               & rows["FSFFARBE"].ToString() == color
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwColor = rw;
                            colordes = GetGB2312Value(rw["FSFFBEZ"].ToString());
                        }
                        break;
                    case "6":
                        break;
                    case "7":
                        break;
                    case "8":
                        query = from rows in dsSource.Tables["MSSTAMM"].AsEnumerable()
                                where rows["MSMAT"].ToString() == Left(art, 14)
                                & rows["MSVAR"].ToString() == var
                                select rows;
                        foreach (DataRow rw in query)
                        {
                            dwArticle = rw;
                            artdes = GetGB2312Value(rw["MSBEZ"].ToString());
                        }
                        break;
                    default:
                        break;
                }
                x = rown; y = 1;
                exAgent.SetCellValue(x, y, pos);

                x = rown; y = 4;
                exAgent.SetCellValue(x, y, art.Trim() + var.Trim() + "(" + artdes.Trim() + ")");

                x = rown; y = 12;
                exAgent.SetCellValue(x, y, color.Trim() + "(" + colordes.Trim() + cref + ")");
                exAgent.SetCellValue(x + 1, y, excolor);
                x = rown; y = 16;//成份
                exAgent.SetCellValue(x, y, composition);
                exAgent.SetCellValue(x + 1, y, structure);
                x = rown; y = 22;
                exAgent.SetCellValue(x, y, size);

                x = rown; y = 24;
                exAgent.SetCellValue(x, y, qty);

                x = rown; y = 27;
                exAgent.SetCellValue(x, y, um);

                x = rown; y = 29;
                exAgent.SetCellValue(x, y, price);

                x = rown; y = 32;
                exAgent.SetCellValue(x, y, tvalue);
                rown++;//填写供应商品种信息
                x = rown; y = 4;
                exAgent.SetCellValue(x, y, exart);
                #region===========Surcharge/Dicount=========
                FillinLineSurcharge(dwEKPOS, x, ref rown);
                #endregion
                var qMU = from DataRow rw in dsSource.Tables["ABPOS"].AsEnumerable()
                          where rw["EKPAUFKRS"].ToString() == dwEKPOS["EKPAUFKRS"].ToString()
                          && rw["EKPAUFLFD"].ToString() == dwEKPOS["EKPAUFLFD"].ToString()
                          && rw["EKPAUFPOS"].ToString() == dwEKPOS["EKPAUFPOS"].ToString()
                          select rw;
                string delstr = "";
                double aqty = 0;
                string part = "";
                int d = 0;
                if (qMU.Count() == 0)
                    rown++;
                int cut = qMU.Count();
                foreach (DataRow rw in qMU)
                {
                    //delstr += "发送到单:" + rw["EKPAUFKRS"].ToString() + "-" + rw["ABPAUFLFD"].ToString();
                    aqty = System.Math.Round(System.Convert.ToDouble(rw["ABAQTYRESERVE"].ToString()), 2);
                    if (um.Trim() == "KG")
                    {
                        aqty = System.Math.Round(aqty * width * 0.01 * gsqm * 0.001, 1);
                    }
                    delstr = "地址:" + GetGB2312Value(rw["LSSTRASSE1"].ToString()).Trim() +":"+ GetGB2312Value(rw["LSAPNA1"].ToString()).Trim() 
                        +"("+ GetGB2312Value(rw["LSAPTEL1"].ToString()).Trim()+")";                    
                    //part = rw["ABPART"].ToString().Trim();
                    part = "(PR:" + rw["ABPAUFKRS"].ToString().Trim() + "-" + Helper.GetSubRollNum(rw["ABPAUFLFD"].ToString().Trim()) + ")" + rw["ABPART"].ToString().Trim()+")";
                    part =  Helper.GetSubRollNum(rw["ABPAUFLFD"].ToString().Trim()) + "-" + rw["ABPART"].ToString().Trim() + "";

                    string mtype = GetMatType(part, art);
                    //delstr += Environment.NewLine;
                    d++;
                    x = rown + d;
                    y = 24;
                    exAgent.SetCellValue(x, y, aqty + ":" + part + " " + mtype);
                    y = 4;
                    exAgent.SetCellValue(x, y, delstr);
                    //y = 27;
                    //exAgent.SetCellValue(x, y, part + " " + mtype);
                    exAgent.RangeCopy(23, 23, x + 1, x + 1);
                }
                rown = rown + d;
            }
            return rown;
        }
        public string GetMatType(string fart, string mart)
        {
            string result = "";
            if (!dsSource.Tables.Contains("MAINFABRIC"))
                return result;

            var query = from DataRow row in dsSource.Tables["MAINFABRIC"].AsEnumerable()
                        where row["ABPART"].ToString().Trim() == fart.Trim()
                        && row["ABAART"].ToString().Trim() == mart.Trim()
                        select row;

            foreach (DataRow row in query)
            {
                result = "配布";
                if (row["FSSZUSCHN"].ToString().Trim() == "J")
                {
                    result = "主面料";
                }
                if (row["FSSZUSCHN"].ToString().Trim() == "")
                {
                    result = "里布";
                }
                if (row["FSSZUSCHN"].ToString().Trim() == "C")
                {
                    result = "衬布";
                }
                if (row["FSSZUSCHN"].ToString().Trim() == "N")
                {
                    result = "配布";
                }
                break;
            }


            return result;
        }
        private void FillInHeadAdd(DataRow row)
        {
            string term = "";
            decimal rate = 0;
            decimal wert = 0;
            int d = 0;
            int x;
            decimal tqty = System.Convert.ToDecimal(row["EKKME1"].ToString()) / 100;
            exAgent.SetCellValue(11, 20, tqty);
            decimal tvalue = System.Convert.ToDecimal(row["EKKWE1"].ToString()) / 100;
            exAgent.SetCellValue(11, 30, tvalue);
            //                EKPZUAB1	EKPZUABPR1	EKPZUABWE1                
            for (int j = 1; j < 6; j++)
            {
                term = row["EKKZUAB" + j].ToString();
                rate = System.Convert.ToDecimal(row["EKKZUABPR" + j].ToString()) / 100;
                wert = System.Convert.ToDecimal(row["EKKZUABWE" + j].ToString()) / 1000;
                if (term.Trim() != "")
                {
                    d++;
                    term = Helper.GetSDDAT(dsSource, "0255",term,"V");
                    x = 11 + d;
                    exAgent.SetCellValue(x, 4, GetGB2312Value(term));
                    if (rate != 0)
                    {
                        exAgent.SetCellValue(x, 24, rate + "%");
                    }
                    if (wert != 0)
                    {
                        exAgent.SetCellValue(x, 27, wert);
                    }
                }
            }
        }
        private void FillinLineSurcharge(DataRow dwEKPOS, int xstart, ref int rown)
        {
            int x = xstart;
            //exAgent.SetCellValue(x, 27, "附加费:" + System.Convert.ToDecimal(dwEKPOS["EKPZUABWERT"].ToString()) / 100);
            string term = "";
            double rate = 0;
            double wert = 0;
            double sur = 0;
            int d = 0;
            int a = 0;        

            double qty = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPME1"].ToString()) * 0.01, 2);
            double tvalue = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPWE1"].ToString()) * 0.01, 2);
            double price = System.Math.Round(System.Convert.ToDouble(dwEKPOS["EKPPRME"].ToString()) * 0.001, 5);
            for (int j = 1; j < 6; j++)
            {
                term = dwEKPOS["EKPZUAB" + j].ToString();
                rate = System.Convert.ToDouble(dwEKPOS["EKPZUABPR" + j].ToString()) / 100;
                wert = System.Convert.ToDouble(dwEKPOS["EKPZUABWE" + j].ToString()) / 1000;
                if (term.Trim() != "")
                {
                    d++;
                    term = Helper.GetSDDAT(dsSource, "0255", term, "V");
                    x = rown + d;
                    //exAgent.SetCellValue(x, 4, GetGB2312Value(term));
                    if (rate != 0)
                    {
                        //exAgent.SetCellValue(x, 24, rate + "%");
                    }
                    if (wert != 0)
                    {
                        exAgent.SetCellValue(x, 32, term+wert);
                    }
                    sur += wert;
                    exAgent.RangeCopy(23, 23, x + 1, x + 1);
                }
            }
            tvalue = price * qty + sur;
            exAgent.SetCellValue(rown-1, 32, tvalue);
            rown = rown + d;
            EKKWE1 +=Convert.ToDecimal(tvalue);
            EKKME1 += Convert.ToDecimal(qty);
        }
        private void FillInEXCEL(DataRow row)
        {
            try
            {
                string value = "";
                Dictionary<string, int> colorpos = new Dictionary<string, int>();
                int x = 1, y = 1;

                x = 5; y = 18;
                //exAgent.SetCellsPicture(5, 17, @"D:\DS40\A1.jpg"); 
                value = row["EKKAUFKRS"].ToString() + "-" + row["EKKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);

                x = 3; y = 30;
                exAgent.SetCellValue(x, y, date);
                FillInHeadAdd(row);
                int rown = FillInEKPOS();
                FillinSing(rown);
                //exAgent.DeleteRows(rown, 2198);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillinSing(int rown = 24, int fixednum = 1)
        {
            int x = 24, y = 1;
            x = rown; y = 1;

            DataRow ekk = dsSource.Tables["EKKOPF"].Rows[0];
            string liefnr = ekk["EKKLIEFNR"].ToString();
            string kond = ekk["EKKKONDSL"].ToString();
            string payment = "";
            var qy = from DataRow rx in dsSource.Tables["LIEFKO"].AsEnumerable()
                     where rx["LSKLIEFNR"].ToString() == liefnr
                     && rx["LSKKONDSL"].ToString().Trim() == kond.Trim()
                     select rx;

            foreach (DataRow row in qy)
            {     //LSKBANKBEZ	LSKBANKORT LSKIBAN
                x = rown + 4; y = 21;
                exAgent.SetCellValue(x, 18, "开户行：");
                exAgent.SetCellValue(x, 21, GetGB2312Value(row["LSKBANKORT"].ToString().Trim()));
                exAgent.SetCellValue(x, 1, "开户行：");
                exAgent.SetCellValue(x, 4, "上海银行胶州路支行");                
                x = rown + 5; y = 21;
                exAgent.SetCellValue(x, 1, "账户：");
                //exAgent.SetCellValue(x, 4, "315914-03000129533");
                exAgent.SetCellValue(x, 18, "账户：");
                exAgent.SetCellValue(x, 21, GetGB2312Value(row["LSKBANKBEZ"].ToString().Trim()));
                payment = row["LSKZAHLBED"].ToString();
                x = rown + 6; y = 21;
                exAgent.SetCellValue(x, 1, "帐号：");
                exAgent.SetCellValue(x, 4, "315914-03000129533");
                exAgent.SetCellValue(x, 18, "帐号：");
                exAgent.SetCellValue(x, 21, GetGB2312Value(row["LSKIBAN"].ToString().Trim()));
                payment=   row["LSKZAHLBED"].ToString();
           
            }
           DataRow dwTemp = INTEXDB.ZABED.NewRow();
            dwTemp["ZABMANDANT"] = "00";
            dwTemp["ZABNR"] = payment;
           DataSet SResult = INTEXDBC.QueryZABED(dwTemp, 1);
           if (SResult.Tables.Count > 0)
           {
               foreach (DataRow rx in SResult.Tables[0].Rows)
               {
                   payment+=" "+GetGB2312Value(rx["ZABBEZ"].ToString());
               }
           }
           exAgent.SetCellValue(9, 5, payment);
            qy = from DataRow rx in dsSource.Tables["LIEFDAT"].AsEnumerable()
                 where rx["LSLIEFNR"].ToString() == liefnr
                 select rx;

            foreach (DataRow row in qy)
            {
                x = 4; y = 4;
                exAgent.SetCellValue(x, y, GetGB2312Value(row["LSNAME1"].ToString().Trim()));
                x = rown + 3; y = 21;
                exAgent.SetCellValue(x, 18, "乙方：");
                exAgent.SetCellValue(x, 21, GetGB2312Value(row["LSNAME1"].ToString().Trim()));
                x = rown + 7;
                exAgent.SetCellValue(x, 1, "地址：");
                exAgent.SetCellValue(x, 4, "上海普陀区金沙江路1340弄57号D区4D栋4层");
                exAgent.SetCellValue(x, 18, "地址：");
                exAgent.SetCellValue(x, 21, GetGB2312Value(row["LSSTRASSE1"].ToString().Trim()));
            }
            x = rown + 3;
            exAgent.SetCellValue(x, 1, "甲方：");
            exAgent.SetCellValue(x, 4, "上海银妆服饰有限公司");
            x = rown + 8;
            exAgent.SetCellValue(x, 1, "法定代表人或委托代理人：");
            exAgent.SetCellValue(x, 18, "法定代表人或委托代理人：");
            x = rown + 9;
            exAgent.SetCellValue(x, 1, "联系人：");
            exAgent.SetCellValue(x, 18, "联系人：");
        }
        private void FillInQPPlan(int rown = 2106)
        {
            int x = 24, y = 1;
            x = rown; y = 1;
            string value = "";
            DataRow ekp = dsSource.Tables["ICC_EKPOS"].Rows[0];
            string art = ekp["EKPART"].ToString();
            var qy = from DataRow rx in dsSource.Tables["WAPROF"].AsEnumerable()
                     where rx["WAPART"].ToString() == art
                     select rx;
            foreach (DataRow row in qy)
            {
                string pos = Helper.GetSubRollNum(row["WAPPOS"].ToString());
                value = row["WAPBEM"].ToString();
                if (value.Trim() == "")
                    value = "0";
                string std = (System.Convert.ToDecimal(value) / 10000).ToString();
                value = row["WAPMIN"].ToString();
                if (value.Trim() == "")
                    value = "0";
                string min = (System.Convert.ToDecimal(value) / 1000).ToString();
                value = row["WAPMAX"].ToString();
                if (value.Trim() == "")
                    value = "0";
                string max = (System.Convert.ToDecimal(value) / 1000).ToString();
                string um = row["WAPME"].ToString();
                exAgent.SetCellValue(x, y, pos);

                exAgent.SetCellValue(x, 3, GetGB2312Value(row["WAPANFART"].ToString() + ":" + row["QPMKURZB"].ToString().Trim()));
                exAgent.SetCellValue(x, 10, min + "~" + max + um);
                exAgent.SetCellValue(x, 17, std);
                x++;
            }
        }

        private string CreateFile(string ordernumer, DataRow row)
        {
            string filename = "";
            string sheetName = "";
            string newfilename = "";
            try
            {
                string pwd = "";
                sheetName = ordernumer;
                newfilename = "采购单_" + ordernumer + ".xls";
                if (DocType == "EKSEND")
                {
                    newfilename = "发料单_" + ordernumer + ".xls";
                }
                

                exAgent.Open(TemplatePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                exAgent.CreateNewSheet(newfilename, Standard, sheetName, pwd);
                filename = exAgent.FileName;
                EKKWE1 = 0;
                EKKME1 = 0;
                switch (Standard)
                {
                    case "EK03PO":
                        FillInPOEXCEL(row);
                        break;
                    case "EK03FP":
                        FillInFPEXCEL(row);
                        break;
                    case "EK03DOC":
                        FillInEXCEL(row);
                        break;
                    case "EKSEND":
                        //FillInPOEXCEL(row);
                        FillInSend(row);
                        break;
                    default:
                        FillInEXCEL(row);
                        break;
                }
                exAgent.SetCellValue(11, 20, EKKME1);
                exAgent.SetCellValue(11, 30, EKKWE1);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                exAgent.Save();
                exAgent.Close();
            }
            return filename;
        }
    }
}